package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import model.Student;
import util.ConnectionUtil;

public class StudentDao {
	/**
	 * 查询学生列表
	 */
	public static ArrayList<Student> queryStudentList() {
		ArrayList<Student> studentList = new ArrayList<>();

		Connection conn = ConnectionUtil.getConnection();
		Statement stmt = null;
		try {

			// 执行查询
			System.out.println(" 实例化Statement对象...");
			stmt = conn.createStatement();
			String sql;
			sql = "SELECT stu_id, stu_name, stu_age, class_name, class_major, is_boy FROM student join clazz "
					+ "on student.class_id = clazz.class_id";
			ResultSet rs = stmt.executeQuery(sql);

			// 展开结果集数据库
			while (rs.next()) {
				// 通过字段检索
				int id = rs.getInt("stu_id");
				String name = rs.getString("stu_name");
				int age = rs.getInt("stu_age");
				String classname = rs.getString("class_name");
				String major = rs.getString("class_major");
				int is_boy = rs.getInt("is_boy");
				String gender = "男";
				if (is_boy == 1) {
					gender = "男";
				} else {
					gender = "女";
				}
				Student student = new Student(id, name, age, classname, major, gender);
				studentList.add(student);
			}
			// 完成后关闭
			rs.close();
			stmt.close();
		} catch (SQLException se) {
			// 处理 JDBC 错误
			se.printStackTrace();
		} catch (Exception e) {
			// 处理 Class.forName 错误
			e.printStackTrace();
		} finally {
			// 关闭资源
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException se2) {
			}
		}

		System.out.println("查询成功!");
		return studentList;

	}

	public static void addUseStatement(Student student) {
		Connection connection = ConnectionUtil.getConnection();
		try {
			Statement statement = connection.createStatement();

			int gender = 1;
			if (student.getGender().equals("男")) {
				gender = 1;
			} else {
				gender = 0;
			}

			int class_id = ClassDao.getClassId(student.getClassname());
			String sql = "INSERT INTO student VALUES (" + student.getId() + ", '" + student.getName() + "', "
					+ student.getAge() + ", " + class_id + ", " + gender + ");";
			System.err.println(sql);
			statement.execute(sql);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public static void addUsePrepareStatement(Student student) {
		Connection conn = ConnectionUtil.getConnection();
		String sql = "insert into student values(?,?,?,?,?)";
		try {
			PreparedStatement psta = conn.prepareStatement(sql);
			psta.setInt(1, student.getId());
			psta.setString(2, student.getName());
			psta.setInt(3, student.getAge());

			int class_id = ClassDao.getClassId(student.getClassname());
			psta.setInt(4, class_id);

			int gender = 1;
			if (student.getGender().equals("男")) {
				gender = 1;
			} else {
				gender = 0;
			}
			psta.setInt(5, gender);

			int result = psta.executeUpdate();
			if (result > 0) {
				System.out.println("插入成功");
			} else {
				System.out.println("插入失败");
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public static void del(String name) {
		Connection conn = ConnectionUtil.getConnection();

		String sql = "delete from student where stu_name = '" + name + "'";

		try {
			Statement stat = conn.createStatement();
			int result = stat.executeUpdate(sql);
			if (result > 0) {
				System.out.println("删除成功");
			} else {
				System.out.println("删除失败");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public static void update(String name, int column, String value) {
		Connection conn = ConnectionUtil.getConnection();
		String sql = null;
		switch (column) {
		case 1:
			sql = "update student SET stu_name = '"+value+"'  where stu_name = '"+name+"'";	
			break;

		case 2:

			break;

		case 3:

			break;

		default:
			break;
		}
		
		try {
			Statement stat = conn.createStatement();
			int result = stat.executeUpdate(sql);
			if(result > 0) {
				System.out.println("更新成功");
			}else {
				System.out.println("更新失败");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public static int getStuIdWithName(String stu_name) {
		Connection conn =ConnectionUtil.getConnection();
		String sql = "select stu_id from student where stu_name = '"+stu_name+"'";
		try {
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sql);
			if(rs.next()) {
				int stu_id = rs.getInt(1);
				return stu_id;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return 1100;
	}
}
